<?php
class Chart extends AppModel {
	var $name = 'Chart';
	var $displayField = 'name';
	//The Associations below have been created with all possible keys, those that are not needed can be removed

	var $hasMany = array(
		'ChartParameter' => array(
			'className' => 'ChartParameter',
			'foreignKey' => 'chart_id',
			'dependent' => false,
			'conditions' => '',
			'fields' => '',
			'order' => '',
			'limit' => '',
			'offset' => '',
			'exclusive' => '',
			'finderQuery' => '',
			'counterQuery' => ''
		)
	);

    public function generateData($chart_id, $params) {
        // obtain the chart and related parameter data
        $chart = $this->find('first', array(
            'conditions' => array('Chart.id' => $chart_id),
            'recursive' => -1
        ));
        
        $data = $this->fetchData($chart_id, $params);
        
        $categories = array();
        $series = array();
        
        foreach($data as $rows) {
            if( is_array($rows) ) {
                foreach($rows as $columns) {
                    $categories[] = $columns[0][$chart['Chart']['x_axis_col_name']];
                    $keys = array_keys($columns[0]);
                    
                    for($i = 1; $i < count($columns[0]); $i++) {
                        if( !isset($series[$i - 1]) || !is_array($series[$i - 1]) ) {
                            $series[$i - 1] = array('name' => $keys[$i], 'data' => array());
                        }
                        
                        // data needs to be in numerical format for charts to be able to draw successfully
                        $series[$i - 1]['data'][] = doubleval($columns[0][ $keys[$i] ]);
                    }
                }
            }
        }
        
        $output = array(
            'chart' => $chart['Chart'],
            'categories' => $categories,
            'series' => $series
        );
        
        return $output;
    }
    
    private function fetchData($chart_id, $params) {
        $chart = $this->find('first', array(
            'conditions' => array('Chart.id' => $chart_id)
        ));
        
        $query = $chart['Chart']['query'];
        
        // replace parameter variables
        foreach($chart['ChartParameter'] as $parameter) {
            $query = str_replace($parameter['name'], $params[$parameter['name']], $query);
        }
        
        // break up all the queries using the semi-colon separator and run them independently
        $queries = explode(';', $query);
		$results = array();
        
        $dataSource = $this->getDataSource();
		$dataSource->begin($this);

		try {
			foreach ($queries as $q) {
                if( trim($q) != "" ) {
                    $results[] = $this->query(trim($q));
                }
			}

			$dataSource->commit($this);
		} catch (Exception $e) {
			$dataSource->rollback();
		}

		return $results;
    }
}
?>